Airlines Review Analysis
This notebook is dedicated to perform an Exploratory Data Analysis on an airlines review dataset. The goal of this analysis is to uncover insights on customer satisfaction and factors that affect airline rating. By examining reviews from various perspectives, this analysis aims to provide actionable insights into what factors most influence passengers’ decisions to recommend an airline, as well as how verified and unverified reviews differ in their impact on overall ratings.
Importing necessary libraries
import pandas as pd
import re
from textblob import TextBlob
from wordcloud import WordCloud, STOPWORDS
from collections import Counter
import requests
import plotly.io as pio
import plotly.express as px
import plotly.graph_objects as go
import plotly.colors as colors
from plotly.subplots import make_subplots
Reading in data from the csv
dataset_path = 'airline reviews_countries updated.csv'
df = pd.read_csv(dataset_path)
df.head()
| Overall_Rating | Unnamed | Airline Name | Review_Title | Review Date | Verified | Review | Aircraft | Type Of Traveller | Seat Type | ... | Country of Arrival | Date Flown | Seat Comfort | Cabin Staff Service | Food & Beverages | Ground Service | Inflight Entertainment | Wifi & Connectivity | Value For Money | Recommended | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 9 | 0 | AB Aviation | "pretty decent airline" | 11th November 2019 | True | Moroni to Moheli. Turned out to be a pretty de... | NaN | Solo Leisure | Economy Class | ... | Comoro Islands | November 2019 | 4.0 | 5.0 | 4.0 | 4.0 | NaN | NaN | 3.0 | yes |
| 1 | 1 | 1 | AB Aviation | "Not a good airline" | 25th June 2019 | True | Moroni to Anjouan. It is a very small airline.... | E120 | Solo Leisure | Economy Class | ... | Comoro Islands | June 2019 | 2.0 | 2.0 | 1.0 | 1.0 | NaN | NaN | 2.0 | no |
| 2 | 1 | 2 | AB Aviation | "flight was fortunately short" | 25th June 2019 | True | Anjouan to Dzaoudzi. A very small airline and ... | Embraer E120 | Solo Leisure | Economy Class | ... | Mayotte | June 2019 | 2.0 | 1.0 | 1.0 | 1.0 | NaN | NaN | 2.0 | no |
| 3 | 1 | 3 | Adria Airways | "I will never fly again with Adria" | 28th September 2019 | False | Please do a favor yourself and do not fly with... | NaN | Solo Leisure | Economy Class | ... | Kosovo | September 2019 | 1.0 | 1.0 | NaN | 1.0 | NaN | NaN | 1.0 | no |
| 4 | 1 | 4 | Adria Airways | "it ruined our last days of holidays" | 24th September 2019 | True | Do not book a flight with this airline! My fri... | NaN | Couple Leisure | Economy Class | ... | Netherlands | September 2019 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | no |
5 rows × 24 columns
Get a summary of the data
df.info()
<class 'pandas.core.frame.DataFrame'> RangeIndex: 23171 entries, 0 to 23170 Data columns (total 24 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 Overall_Rating 23171 non-null object 1 Unnamed 23171 non-null int64 2 Airline Name 23171 non-null object 3 Review_Title 23171 non-null object 4 Review Date 23171 non-null object 5 Verified 23171 non-null bool 6 Review 23171 non-null object 7 Aircraft 7108 non-null object 8 Type Of Traveller 19433 non-null object 9 Seat Type 22075 non-null object 10 Route 19343 non-null object 11 Departure 23171 non-null object 12 Arrival 23171 non-null object 13 Country of Departure 23171 non-null object 14 Country of Arrival 23171 non-null object 15 Date Flown 19417 non-null object 16 Seat Comfort 19016 non-null float64 17 Cabin Staff Service 18911 non-null float64 18 Food & Beverages 14500 non-null float64 19 Ground Service 18378 non-null float64 20 Inflight Entertainment 10829 non-null float64 21 Wifi & Connectivity 5920 non-null float64 22 Value For Money 22105 non-null float64 23 Recommended 23171 non-null object dtypes: bool(1), float64(7), int64(1), object(15) memory usage: 4.1+ MB
df.shape
(23171, 24)
Data cleaning
# checking for duplicates
duplicates = df.duplicated()
print(f"Number of duplicate rows: {duplicates.sum()}")
df[duplicates]
Number of duplicate rows: 0
| Overall_Rating | Unnamed | Airline Name | Review_Title | Review Date | Verified | Review | Aircraft | Type Of Traveller | Seat Type | ... | Country of Arrival | Date Flown | Seat Comfort | Cabin Staff Service | Food & Beverages | Ground Service | Inflight Entertainment | Wifi & Connectivity | Value For Money | Recommended |
|---|
0 rows × 24 columns
# dropping columns that will not be utilised
df.drop(['Overall_Rating', 'Departure', 'Arrival', 'Route'], axis=1, inplace=True)
# checking for null values
df.isnull().sum()
Unnamed 0 Airline Name 0 Review_Title 0 Review Date 0 Verified 0 Review 0 Aircraft 16063 Type Of Traveller 3738 Seat Type 1096 Country of Departure 0 Country of Arrival 0 Date Flown 3754 Seat Comfort 4155 Cabin Staff Service 4260 Food & Beverages 8671 Ground Service 4793 Inflight Entertainment 12342 Wifi & Connectivity 17251 Value For Money 1066 Recommended 0 dtype: int64
# filling in missing values for categorical columns that will be utilised in this analysis
mode_type_of_traveller = df['Type Of Traveller'].mode()[0]
df['Type Of Traveller'].fillna(mode_type_of_traveller, inplace=True)
mode_seat_type = df['Seat Type'].mode()[0]
df['Seat Type'].fillna(mode_seat_type, inplace=True)
numerical_columns = df.select_dtypes(include=['float64']).columns
# filling in missing values for numerical columns that will be utilised in this analysis
for column in numerical_columns:
group_means = df.groupby('Airline Name')[column].transform('mean')
group_means.fillna(df[column].mean(), inplace=True)
df[column] = df[column].fillna(group_means).round(0).astype(float)
# filling in missing values for Date Flown using backward fill
df['Date Flown'] = df.groupby('Airline Name')['Date Flown'].fillna(method='ffill')
df['Date Flown'] = df.groupby('Airline Name')['Date Flown'].fillna(method='bfill')
df.isnull().sum()
Unnamed 0 Airline Name 0 Review_Title 0 Review Date 0 Verified 0 Review 0 Aircraft 16063 Type Of Traveller 0 Seat Type 0 Country of Departure 0 Country of Arrival 0 Date Flown 310 Seat Comfort 0 Cabin Staff Service 0 Food & Beverages 0 Ground Service 0 Inflight Entertainment 0 Wifi & Connectivity 0 Value For Money 0 Recommended 0 dtype: int64
df['Route'] = df['Country of Departure'] + ' to ' + df['Country of Arrival']
# re-adding the previously dropped ‘Overall Ratings’ column, this time by calculating it as the sum of the Service Category ratings
df['Overall Rating'] = df[['Seat Comfort', 'Cabin Staff Service', 'Food & Beverages', 'Ground Service', 'Inflight Entertainment', 'Wifi & Connectivity', 'Value For Money']].sum(axis=1)
df.head(2)
| Unnamed | Airline Name | Review_Title | Review Date | Verified | Review | Aircraft | Type Of Traveller | Seat Type | Country of Departure | ... | Seat Comfort | Cabin Staff Service | Food & Beverages | Ground Service | Inflight Entertainment | Wifi & Connectivity | Value For Money | Recommended | Route | Overall Rating | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 0 | AB Aviation | "pretty decent airline" | 11th November 2019 | True | Moroni to Moheli. Turned out to be a pretty de... | NaN | Solo Leisure | Economy Class | Comoro Islands | ... | 4.0 | 5.0 | 4.0 | 4.0 | 2.0 | 2.0 | 3.0 | yes | Comoro Islands to Comoro Islands | 24.0 |
| 1 | 1 | AB Aviation | "Not a good airline" | 25th June 2019 | True | Moroni to Anjouan. It is a very small airline.... | E120 | Solo Leisure | Economy Class | Comoro Islands | ... | 2.0 | 2.0 | 1.0 | 1.0 | 2.0 | 2.0 | 2.0 | no | Comoro Islands to Comoro Islands | 12.0 |
2 rows × 22 columns
Which airlines receive the highest and lowest ratings, in terms of average overall ratings?
# calculate the mean overall rating, grouped by airline name
airline_ratings = df.groupby('Airline Name')['Overall Rating'].mean().reset_index().round(1)
airline_ratings = airline_ratings.sort_values(by='Overall Rating', ascending=False)
top_5_airlines = airline_ratings.head(5).copy()
bottom_5_airlines = airline_ratings.tail(5).copy()
# new columns for top and bottom airlines
top_5_airlines['Type'] = 'Top 5 Airlines'
bottom_5_airlines['Type'] = 'Bottom 5 Airlines'
combined_airlines = pd.concat([top_5_airlines, bottom_5_airlines])
fig = go.Figure()
# bars for the top 5 airlines
fig.add_trace(
go.Bar(
x=top_5_airlines['Airline Name'],
y=top_5_airlines['Overall Rating'],
marker_color='skyblue',
name="Top 5 Airlines"
)
)
# bars for bottom 5 airlines
fig.add_trace(
go.Bar(
x=bottom_5_airlines['Airline Name'],
y=bottom_5_airlines['Overall Rating'],
marker_color='#B0C4DE',
name="Bottom 5 Airlines"
)
)
fig.update_layout(
height=700,
title_text="Top and Bottom 5 Airlines by Average Overall Rating",
xaxis_title="Airline Name",
yaxis_title="Average Overall Rating",
showlegend=True,
plot_bgcolor='rgba(0,0,0,0)'
)
fig.show()
The above analysis shows a clear distinction between the top 5 and bottom 5 airlines based on average of the total numerical ratings. The top 5 airlines exhibit significantly higher average rating, with ratings in the range of 29 to 30, compared to the bottom 5 with ratings from 5 to 8.
What are the common review and sentiments in passenger reviews for the top and bottom airline?
The following uses wordcloud text analysis to determine the frequent words used in the review title for the top and bottom airline. This will give us a clearer picture of what’s driving those high or low ratings.
# filter for StarFlyer and Arkefly reviews
StarFlyer_df = df[df['Airline Name'] == 'StarFlyer']
Arkefly_df = df[df['Airline Name'] == 'Arkefly']
# define custom stopwords
custom_stopwords = {'i','the','only','t','had','would', 'and', 'is', 'to', 'in', 'it', 'a', 'for', 'of', 'on', 'this',
'that', 'with', 'as', 'at', 'by', 'are', 'was', 'an', 'be',
'have', 'from', 'but', 'not', 'or', 'all', 'can', 'you',
'your', 'my', 'if', 'I', 'me', 'he', 'she', 'we', 'they',
'what', 'there', 'their', 'this', 'its', 'so', 'like',
'no', 'yes', 'do', 'will', 'more', 'than', 'up', 'when', 'flight','starflyer','arkefly','very','were'}
# function to extract most common words
def get_common_words(df, num_words=10):
# combine all reviews into a single string
text = ' '.join(df['Review'])
# remove punctuation and split into words
words = re.findall(r'\w+', text.lower())
filtered_words = [word for word in words if word not in custom_stopwords] # exclude stopwords
common_words = Counter(filtered_words).most_common(num_words) # get the most common words
return pd.DataFrame(common_words, columns=['Word', 'Frequency'])
# get common words for both airliens
common_words_starflyer = get_common_words(StarFlyer_df)
common_words_arkefly = get_common_words(Arkefly_df)
# plot common words for StarFlyer
fig1 = px.bar(common_words_starflyer, x='Word', y='Frequency',
title='Most Common Words in StarFlyer Reviews',
labels={'Word': 'Common Words', 'Frequency': 'Frequency'},
color='Frequency',
color_continuous_scale=px.colors.sequential.Blues,
template='plotly_white')
fig1.show()
# plot common words for Arkefly
fig2 = px.bar(common_words_arkefly, x='Word', y='Frequency',
title='Most Common Words in Arkefly Reviews',
labels={'Word': 'Common Words', 'Frequency': 'Frequency'},
color='Frequency',
color_continuous_scale=px.colors.sequential.Reds,
template='plotly_white')
fig2.show()
Based on the StarFlyer results, common words like “service,” “tokyo,” “staff,” “leather,” “attendants,” “personal,” and “seat” give us a clear idea of why the airline has higher reviews. It shows that passengers are happy with the good service from the staff, the comfort of the leather seats, and that they appreciate the routes to major destinations like Tokyo.
For Arkefly, words like “seats,” “class,” “comfort,” “food,” “amsterdam,” “service,” and “premium” stand out. Since Arkefly has poor ratings, we can assume that passengers are not satisfied with the comfort, food quality, and overall service, especially when it comes to premium offerings. This suggests that Arkefly is not meeting expectations in these key areas.
How do different seat types fare in terms of ratings?
# group the dataset by Seat Type and calculate the mean for each service category rating
avg_ratings_seattype = df.groupby('Seat Type').agg(
Seat_Comfort=('Seat Comfort', 'mean'),
Cabin_Staff_Service=('Cabin Staff Service', 'mean'),
Food_Beverages=('Food & Beverages', 'mean'),
Ground_Service=('Ground Service', 'mean'),
Inflight_Entertainment=('Inflight Entertainment', 'mean'),
Wifi_Connectivity=('Wifi & Connectivity', 'mean'),
Value_For_Money=('Value For Money', 'mean'),
Review_Count=('Overall Rating', 'count') # count number of reviews for each seat type
).reset_index().round(1)
# rename the columns to remove underscores
avg_ratings_seattype.rename(columns={
'Seat_Comfort': 'Seat Comfort',
'Cabin_Staff_Service': 'Cabin Staff Service',
'Food_Beverages': 'Food Beverages',
'Ground_Service': 'Ground Service',
'Inflight_Entertainment': 'Inflight Entertainment',
'Wifi_Connectivity': 'Wifi Connectivity',
'Value_For_Money': 'Value For Money'
}, inplace=True)
avg_ratings_seattype
| Seat Type | Seat Comfort | Cabin Staff Service | Food Beverages | Ground Service | Inflight Entertainment | Wifi Connectivity | Value For Money | Review_Count | |
|---|---|---|---|---|---|---|---|---|---|
| 0 | Business Class | 3.3 | 3.6 | 3.1 | 3.0 | 2.6 | 2.2 | 3.1 | 2098 |
| 1 | Economy Class | 2.5 | 2.8 | 2.4 | 2.3 | 1.8 | 1.6 | 2.4 | 20241 |
| 2 | First Class | 3.5 | 3.7 | 3.1 | 3.1 | 2.4 | 2.3 | 3.2 | 186 |
| 3 | Premium Economy | 2.7 | 2.9 | 2.5 | 2.5 | 2.3 | 2.0 | 2.5 | 646 |
color_palette = [
'#54bebe',
'#76c8c8',
'#98d1d1',
'#badbdb',
'#e4bcad',
'#df979e',
'#d7658b'
]
# melt df for stacking
avg_ratings_seattype_melted = avg_ratings_seattype.melt(
id_vars=['Seat Type', 'Review_Count'],
value_vars=['Seat Comfort', 'Cabin Staff Service', 'Food Beverages',
'Ground Service', 'Inflight Entertainment',
'Wifi Connectivity', 'Value For Money'],
var_name='Service_Category',
value_name='Rating'
)
# stacked bar chart
fig = px.bar(
avg_ratings_seattype_melted,
x='Seat Type',
y='Rating',
color='Service_Category', # stack by service category
title='Average Ratings by Seat Type and Service Category',
labels={'Rating': 'Average Rating', 'Seat Type': 'Seat Type',
'Service_Category': 'Service Category'},
hover_data={'Review_Count': True}, # show the count of reviews in the tooltip
color_discrete_sequence=color_palette,
template='plotly_white'
)
# update x-axis to reflect the sorted order
fig.update_layout(xaxis=dict(categoryorder='total descending', categoryarray=avg_ratings_seattype['Seat Type'].tolist()))
fig.show()
The chart shows that first class has the highest average rating, while economy class is rated the lowest. However, economy has the most reviews, likely because it’s the most common seat type. First class, with fewer reviews, caters to a smaller group of travellers.
First class also seems to score the highest for all service category ratings, except for inflight entertainment, which was surpassed by business class. Economy class appears to score the lowest for all service category ratings. The higher volume of reviews for economy class could indicate a greater diversity of experiences, possibly leading to lower average ratings across service categories.
What factors influence whether a passenger recommends an airline?
# ensure 'Recommended' column is a string and handle NaN values
df['Recommended'] = df['Recommended'].fillna('').astype(str).str.strip().str.lower()
# convert 'Recommended' to binary format
df['Recommended'] = df['Recommended'].map({'yes': 1, 'no': 0})
# selecting relevant features
features = ['Seat Comfort', 'Cabin Staff Service', 'Food & Beverages',
'Ground Service', 'Inflight Entertainment', 'Wifi & Connectivity',
'Value For Money']
# corr matrix
corr_matrix = df[features + ['Recommended']].corr()
# heatmap
fig = px.imshow(corr_matrix,
color_continuous_scale='Greens', # Choose a color scale
title='Correlation Matrix',
labels=dict(x='Features', y='Features', color='Correlation Coefficient'),
x=corr_matrix.columns,
y=corr_matrix.columns)
fig.show()
Based on the heatmap results, we can see that value for money and ground service are the top factors influencing whether a passenger will recommend an airline.
What is the % of verified vs unverified reviews, and how does the verification status affect the ratings?
# calculate overall percentage of verified reviews
total_reviews = len(df)
verified_reviews = df['Verified'].sum()
non_verified_reviews = total_reviews - verified_reviews
# calculate average overall rating for verified and non-verified reviews
average_rating_verified = df[df['Verified'] == True]['Overall Rating'].mean().round(1)
average_rating_non_verified = df[df['Verified'] == False]['Overall Rating'].mean().round(1)
# data for pie chart
labels = ['Verified', 'Non-Verified']
sizes = [verified_reviews, non_verified_reviews]
avg_ratings = [average_rating_verified, average_rating_non_verified]
# pie chart
pie_data = pd.DataFrame({
'Label': labels,
'Count': sizes,
'Average Rating': avg_ratings
})
# subplots
fig = make_subplots(
rows=1, cols=2,
subplot_titles=("Verified vs Non-Verified Reviews", "Distribution of Ratings by Verification Status"),
specs=[[{'type': 'pie'}, {'type': 'box'}]] # Specify types for each subplot
)
# add pie chart
fig.add_trace(
go.Pie(
labels=pie_data['Label'],
values=pie_data['Count'],
hole=0.3, # Optional: to create a donut chart
textinfo='label+percent',
hovertemplate="<b>%{label}</b><br>Number of Reviews: %{value}<br>Average Rating: %{customdata[0]:.1f}",
customdata=pie_data['Average Rating'],
marker=dict(colors=['#A0C4E1', '#7ED8B4'])
),
row=1, col=1
)
# add box plot
fig.add_trace(
go.Box(
y=df['Overall Rating'],
x=df['Verified'],
jitter=0.5, # Random jitter to avoid overlap
pointpos=-1.8, # Position of points
name='Overall Rating',
marker=dict(color='#A0C4E1'),
boxmean=True, # Add mean line,
),
row=1, col=2
)
fig.update_layout(
plot_bgcolor='white', # Background color of the plot area
paper_bgcolor='white', # Background color of the entire figure
xaxis=dict(
tickvals=[True, False],
ticktext=['Verified', 'Non-Verified'],
gridcolor='lightgrey' # Lighter grey for x-axis grid lines
),
yaxis=dict(
gridcolor='lightgrey' # Lighter grey for y-axis grid lines
)
)
fig.show()
From the pie chart, we can observe that verified reviews make up slightly more at 53.2%. However, despite having more verified reviews, the average rating for these is lower by 0.2 compared to non-verified reviews. The box plot shows that the overall distribution of ratings between the two groups is quite similar, though the verified reviews have a lower Q1 value by 1.
This suggests that while most reviews are verified, the verification process doesn’t necessarily translate into higher ratings, and the range of ratings is still quite comparable.
Export data to csv, to be used on Tableau
df.to_csv('revised_airline_review.csv', index=False)